package com.meiyuetao.myt.statistics.web.action;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import lab.s2jh.core.web.SimpleController;

import org.apache.commons.lang3.StringUtils;
import org.apache.struts2.rest.HttpHeaders;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.google.common.collect.Lists;
import com.meiyuetao.myt.statistics.vo.BoxOrderVo;

public class BoxOrderStatisticsController extends SimpleController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public HttpHeaders count() {
        return buildDefaultHttpHeaders("count");
    }

    public HttpHeaders dispatcher() {
        String model = this.getParameter("model");
        if (StringUtils.isBlank(model)) {
            // default
            return this.commodityDetailDatas();
        } else if (model.equals("commodityDetailDatas")) {
            return this.commodityDetailDatas();
        } else if (model.equals("commodityOrderDatas")) {
            return this.commodityOrderDatas();
        }
        return null;
    }

    public HttpHeaders commodityDetailDatas() {
        String date = this.getParameter("date");
        String[] dates = date.split("～");

        MapSqlParameterSource args = new MapSqlParameterSource();
        if (StringUtils.isNotBlank(date) && dates.length >= 2) {
            args.addValue("dateFrom", new DateTime(dates[0].trim()).toString());
            args.addValue("dateTo", new DateTime(dates[1].trim()).toString());
        } else {
            DateTime now = new DateTime();

            args.addValue("dateFrom", now.minusMonths(1).toString());
            args.addValue("dateTo", now.toString());
        }

        // 关键字搜索
        args.addValue("kw", "%" + (this.getParameter("kw") == null ? "" : this.getParameter("kw")) + "%");

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT c.barcode,c.title,tab1.quantity,tab1.order_num FROM( ");
        sql.append(" SELECT row_number () OVER (ORDER BY tab0.quantity DESC) AS order_num,tab0.quantity,tab0.commodity_sid FROM( ");
        sql.append(" SELECT bodc.commodity_sid,SUM (bodc.quantity) AS quantity FROM ");
        sql.append("iyb_box_order bo,iyb_box_order_detail bod,iyb_box_order_detail_commodity bodc ");
        sql.append("where bod.pay_time>=:dateFrom and bod.pay_time<=:dateTo ");
        sql.append("and bo.sid=bodc.order_sid and bodc.order_detail_sid = bod.sid and bodc.is_gift<>1 ");
        sql.append("group by bodc.commodity_sid) tab0 ) tab1, iyb_commodity c where tab1.commodity_sid=c.sid  ");
        sql.append("AND (c.title LIKE :kw OR c.barcode LIKE :kw)");// 关键字搜索SQL部分

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

        final List<BoxOrderVo> boxOrderVos = Lists.newArrayList();
        namedParameterJdbcTemplate.query(sql.toString(), args, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                String barcode = rs.getString("barcode");
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                int quantity = rs.getInt("quantity");
                BoxOrderVo boxOrderVo = new BoxOrderVo(barcode, title, orderNum, quantity);
                boxOrderVos.add(boxOrderVo);
            }
        });
        setModel(buildPageResultFromList(boxOrderVos));
        return buildDefaultHttpHeaders();
    }

    public HttpHeaders commodityOrderDatas() {
        String date = this.getParameter("date");
        String[] dates = date.split("～");

        MapSqlParameterSource args = new MapSqlParameterSource();
        if (StringUtils.isNotBlank(date) && dates.length >= 2) {
            args.addValue("dateFrom", new DateTime(dates[0].trim()).toString());
            args.addValue("dateTo", new DateTime(dates[1].trim()).toString());
        } else {
            DateTime now = new DateTime();

            args.addValue("dateFrom", now.minusMonths(1).toString());
            args.addValue("dateTo", now.toString());
        }

        // 关键字搜索
        args.addValue("kw", "%" + (this.getParameter("kw") == null ? "" : this.getParameter("kw")) + "%");

        StringBuilder sql = new StringBuilder();
        sql.append("select c.barcode,c.title,tab1.quantity ,tab1.order_num from ( ");
        sql.append(" select row_number()over(order by tab0.quantity DESC) as order_num,tab0.quantity,tab0.commodity_sid from( ");

        sql.append("select bodc.commodity_sid,sum(bodc.quantity)as quantity from ");
        sql.append("iyb_box_order bo,iyb_box_order_detail_commodity bodc ");
        sql.append("where bo.order_time>=:dateFrom and bo.order_time<:dateTo ");
        sql.append("and bo.sid=bodc.order_sid and bodc.is_gift<>1 ");
        sql.append("group by bodc.commodity_sid) tab0 ) tab1, iyb_commodity c where tab1.commodity_sid=c.sid  ");
        sql.append("AND (c.title LIKE :kw OR c.barcode LIKE :kw)");// 关键字搜索SQL部分

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

        final List<BoxOrderVo> boxOrderVos = Lists.newArrayList();
        namedParameterJdbcTemplate.query(sql.toString(), args, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                String barcode = rs.getString("barcode");
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                int quantity = rs.getInt("quantity");
                BoxOrderVo boxOrderVo = new BoxOrderVo(barcode, title, orderNum, quantity);
                boxOrderVos.add(boxOrderVo);
            }
        });

        setModel(buildPageResultFromList(boxOrderVos));
        return buildDefaultHttpHeaders();
    }

    protected <S> Page<S> buildPageResultFromList(List<S> list) {
        Page<S> page = new PageImpl<S>(list);
        return page;
    }

    public Map<String, String> getModelMap() {
        Map<String, String> map = new LinkedHashMap<String, String>();
        map.put("commodityDetailDatas", "商品销量排名");
        map.put("commodityOrderDatas", "商品下单量排名");
        return map;
    }
}